USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetOpportunityReferralXml')
	DROP FUNCTION dbo.GetOpportunityReferralXml
GO


CREATE FUNCTION [dbo].[GetOpportunityReferralXml] ()
RETURNS VARCHAR(MAX)
AS
BEGIN
	
	declare 
		@xml			varchar(max)

	SET @xml	= '<OPPREFERRALS><entry id=""></entry>'

	select @xml = @xml + '<entry id="' + CAST(OppReferral AS VARCHAR) + '"><![CDATA[' +  ReferralDesc + ']]></entry>'
	FROM TR_OPPREFERRAL order by ReferralDesc
	set @xml = @xml + '</OPPREFERRALS>'


	return @xml

END
GO

-- select dbo.GetOpportunityReferralXml()